clear all

// SET MACROS
global Input  "Y:/limited/Michigan_CTE/funding_change/data_derived/imported"
global Output "Y:/limited/Michigan_CTE/funding_change/data_derived"

// LOAD DATA
use $Input/act_sat_score.dta

// RENAME VARIABLES
rename ric student
rename actcompositestd act_composite
rename actenglishstd act_english
rename actreadingstd act_reading
rename actmathstd act_math
rename satcompositestd sat_composite
rename satreadingwritingstd sat_reading
rename satmathstd sat_math

// CLEAN VARIABLES

// LAST NONMISSING SAT OR ACT SCORE
foreach var of varlist act_comp act_english act_reading act_math sat_composite sat_reading sat_math {
  bys student (year): gen `var'_g11 = `var' if !missing(`var')
  by  student (year): replace `var'_g11 = `var'_g11[_n-1] if missing(`var') & _n > 1
  by  student (year): replace `var'_g11 = `var'_g11[_N]
}

// KEEP VARIABLES
keep student *_g11

// DROP DUPLICATES
gduplicates drop

// FORMAT VARIABLES
format student %16.0f

// VARIABLE LABELS
label var act_composite_g11 "ACT composite score in Grade 11"
label var act_english_g11 "ACT English score in Grade 11"
label var act_reading_g11 "ACT reading score in Grade 11"
label var act_math_g11 "ACT math score in Grade 11"
label var sat_composite_g11 "SAT composite score in Grade 11"
label var sat_reading_g11 "SAT reading score in Grade 11"
label var sat_math_g11 "SAT math score in Grade 11"

// SORT & SAVE
sort student
compress
save ${Output}/act_sat_score_g11.dta, replace
